package com.starbaba.invitate.service;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.invitate.model.UserInvitateDetails;
import com.starbaba.stat.utils.Constant;

@SuppressWarnings("rawtypes")
@Component
public class UserInvitateDetailsService {

	static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);  //正式stat
	private BaseDao daoLoan = new BaseDao("tt1");  ///数据库loan
	
	
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String invitatorMobile = (String) paramsMap.get("invitatorMobile");
		String inviteeMobile = (String) paramsMap.get("inviteeMobile");
		String inviteeStatus = (String) paramsMap.get("inviteeStatus"); 
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(startTime)  && !"".equals(startTime)) {
			whereParams.append("AND invi.utime >= '" + startTime + " 00:00:00' ");
		}
		if (StringUtils.isNotEmpty(endTime)  && !"".equals(endTime)) {
			whereParams.append("AND invi.utime <= '" + endTime + " 23:59:59' ");
		}
		if (StringUtils.isNotEmpty(invitatorMobile) && !"".equals(invitatorMobile)) {
			whereParams.append("AND parent_u.mobnum LIKE '%" + invitatorMobile + "%' ");
		}
		if (StringUtils.isNotEmpty(inviteeMobile) && !"".equals(inviteeMobile)) {
			whereParams.append("AND son_u.mobnum LIKE '%" + inviteeMobile + "%' ");
		}
		if ("1".equals(inviteeStatus)) {  //已登记
			whereParams.append("AND (info.createtime IS NULL AND son_u.activetime IS NULL) ");
		}
		if ("2".equals(inviteeStatus)) {  //已激活
			whereParams.append("AND (info.createtime IS NULL AND son_u.activetime IS NOT NULL) ");
		}
		if ("3".equals(inviteeStatus)) {  //邀请成功
			whereParams.append("AND info.createtime IS NOT NULL ");
		}
		
		StringBuilder listSql = new StringBuilder("SELECT invi.cdate time,parent_u.mobnum invitatorMobile,son_u.mobnum inviteeMobile,"
				+ "CASE WHEN info.createtime IS NOT NULL THEN '邀请成功' WHEN son_u.activetime IS NOT NULL THEN '已激活' ELSE '已登记' END AS inviteeStatus,"
				+ "invi.ctime successTime,son_u.activetime activityTime,info.createtime infoTime "
				+ "FROM loan_invitate invi LEFT OUTER JOIN comm_user parent_u ON invi.invitator = parent_u.id "
				+ "LEFT OUTER JOIN comm_user son_u ON invi.invitee = son_u.id "
				+ "LEFT OUTER JOIN loan_user_info info ON invi.invitee = info.userid "
				+ "WHERE invi.type = 1 ");
		listSql.append(whereParams);
		listSql.append("order by invi.cdate desc ");
		
		String sqlCount = "select count(1) from (" + listSql.toString() + ") z ";
		if (start != null && length != null) {
			listSql.append("limit " + start + ", " + length);
		}
		int count = daoLoan.getInt(sqlCount);
		
		List<UserInvitateDetails> list = daoLoan.list(listSql.toString(), UserInvitateDetails.class);
		
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		return list;
	}

	
}
